# @Desc    : do what 
# @Author  : meihongliang  
# @Time    : 2025-04-01 21:32
import time

import pymysql
from pymysql import cursors

DB_NAMES = ['appfactory', 'app_a2b', 'app_a3b']

base_db_config = {
    'host': 'localhost',
    'user': 'username',
    'password': 'password',
    'charset': 'utf8mb4'
}

# 环境配置（按照您提供的结构）
config = {
    "1": {
        "mysql_port": 3306,
    },
    "2": {
        "mysql_port": 3307,
    },
    "3": {
        "mysql_port": 3308,
    }
}

# 要操作的表名和查询条件
TABLE_NAME = 'your_table_name'
QUERY_CONDITION = 'WHERE status = 1 LIMIT 30'  # 从源表查询数据的条件
DELETE_CONDITION = 'WHERE date < "2023-01-01"'  # 删除目标表数据的条件


def get_select_sql_by_db_name(db_name):
    """查询标准的数据的sql"""
    sql = None
    if db_name == 'appfactory':
        # where (id='' OR id = '')
        sql = f"SELECT * FROM {db_name} {QUERY_CONDITION}"
    if db_name == 'crimi':
        sql = f"SELECT * FROM {db_name} {QUERY_CONDITION}"
    if db_name == 'sc_sflow':
        sql = f"SELECT * FROM {db_name} {QUERY_CONDITION}"
    return sql


def get_delete_sql_by_db_name(db_name):
    """查询标准的数据的sql"""
    sql = None
    if db_name == 'appfactory':
        # where (id='' OR id = '')
        sql = f"SELECT * FROM {db_name} {QUERY_CONDITION}"
    if db_name == 'crimi':
        sql = None
    if db_name == 'sc_sflow':
        sql = None
    return sql


def get_insert_sql_by_db_name(db_name):
    """查询标准的数据的sql"""
    sql = None
    if db_name == 'appfactory':
        # where (id='' OR id = '')
        sql = f"SELECT * FROM {db_name} {QUERY_CONDITION}"
    if db_name == 'crimi':
        sql = None
    if db_name == 'sc_sflow':
        sql = None
    return sql


def get_source_data(env_id, db_name):
    """从指定环境和数据库获取需要同步的数据"""
    try:
        # 获取环境配置
        env_config = base_db_config.copy()
        env_config["port"] = config[env_id]['mysql_port']  # 设置当前数据库名
        env_config["db"] = db_name  # 设置当前数据库名

        print(f'\t获取当前(模版/标准)数据{env_config}')
        conn = pymysql.connect(**env_config)
        with conn.cursor(cursor=cursors.DictCursor) as cursor:
            # 执行查询
            sql = get_select_sql_by_db_name(db_name)
            if sql:
                cursor.execute(sql)
                data = cursor.fetchall()
                print(f"从环境{env_id}的{db_name}数据库获取到{len(data)}条数据")

                # 打印每条记录的字段内容
                print("\n===== 数据内容详情 =====")
                for i, record in enumerate(data, 1):
                    print(f"\n记录 #{i}:")
                    for field_name, value in record.items():
                        print(f"  {field_name}: {value}")
                    print("-" * 30)  # 分隔线

                for row in data:
                    print(f"ID: {row['id']}, Name: {row['name']}, Age: {row['age']}")

                return data
            else:
                print(f'env_id: {env_id}, db_name: {db_name} 没有查询sql')
                return None
    except Exception as e:
        print(f"\t从环境{env_id}获取数据失败: {e}")
        return None
    finally:
        if 'conn' in locals() and conn.open:
            conn.close()


def sync_to_target(source_data, target_env_id, target_db_name):
    """将数据同步到目标环境数据库"""
    if not source_data:
        print("没有可同步的数据")
        return

    # 获取目标环境配置
    env_config = base_db_config.copy()
    env_config["port"] = config[target_env_id]['mysql_port']  # 设置当前数据库名
    env_config["db"] = target_db_name  # 设置当前数据库名
    print(f'目标数据链接信息\n\t{env_config}')

    # 获取列名用于构建INSERT语句
    columns = list(source_data[0].keys())
    columns_str = ', '.join(columns)
    placeholders = ', '.join(['%s'] * len(columns))

    conn = None
    try:
        conn = pymysql.connect(**target_config)
        with conn.cursor() as cursor:
            # 1. 先删除符合条件的数据
            delete_sql = get_delete_sql_by_db_name(target_db_name)
            if delete_sql:
                cursor.execute(delete_sql)
                deleted_rows = cursor.rowcount
                print(f"从环境{target_env_id}的{target_db_name}数据库中删除了 {deleted_rows} 条数据")
            else:
                print(f'{target_db_name} 数据新增 不需要移除sql')

            # 2. 插入新数据（使用INSERT IGNORE避免重复）
            insert_sql = f"INSERT IGNORE INTO {target_db_name} ({columns_str}) VALUES ({placeholders})"
            cursor.executemany(insert_sql, [tuple(item.values()) for item in source_data])
            conn.commit()
            print(f"成功同步 {cursor.rowcount} 条数据到环境{target_env_id}的{target_db_name}数据库")
    except Exception as e:
        print(f"同步到环境{target_env_id}失败: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn and conn.open:
            conn.close()


def modify_source_data(source_data, db_name):
    if db_name == "appfactory":
        if source_data:
            for item in source_data:
                # 示例1: 添加前缀
                item['name'] = "[前缀]" + item['name']

                # 示例2: 添加后缀（根据需要任选一种）
                # item['name'] = item['name'] + "_后缀"

                # 示例3: 关键字替换
                # item['name'] = item['name'].replace("旧词", "新词")
    if db_name == "app_a2b":
        if source_data:
            for item in source_data:
                # 示例1: 添加前缀
                item['name'] = "[前缀]" + item['name']
    if db_name == "app_a3b":
        if source_data:
            for item in source_data:
                # 示例1: 添加前缀
                item['name'] = "[前缀]" + item['name']
    return source_data


if __name__ == '__main__':
    keys = list(config.keys())
    print(f'全部环境: {keys}')
    sd_project = input("请在上述列表中选择标准环境:")
    if sd_project in keys:
        print(f'您选择的标准环境为={sd_project}')
        keys.remove(sd_project)
        print(f'剩余需要同步的={keys}')
        for target_env in keys:
            db_num = 0
            for db_name in DB_NAMES:
                print(f'\t当前目标环境{target_env},db={db_name}')
                # sd_project=标准环境
                source_data = get_source_data(sd_project, db_name);
                modify_data = modify_source_data(source_data, db_name)
                if modify_data:
                    sync_to_target(modify_data, target_env, db_name)
                else:
                    print(f'\t当前环境={target_env},db_name={db_name}没有数据可以操作')
                db_num += 1
                print(f'\t当前环境{target_env},{db_num}/{len(DB_NAMES)},db={db_name}结束')
            time.sleep(2)
            print(f'\t当前环境{target_env},{db_num}/{len(DB_NAMES)},全部结束。\n')
        print(f'\t目标环境{keys}全部结束')
    else:
        raise Exception("标准环境选择异常")
